package itsm.isperp.workflow.utils;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.dialect.DialectFactory;
import itsm.isperp.framework.data.domain.DataRequest;
import itsm.isperp.module.service.bpm.BpmProcessSettingService;

import java.util.List;
import java.util.Map;

import org.activiti.engine.task.Task;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.jdbc.support.DatabaseType;

/**
 * 
 * bpm 常量数据
 * 
 * @author lizx
 * @date 2013-4-25
 */
public abstract class BpmConst {

  public static final String START_USER_ID = "startUserId";
  public static final String StartUser = "startUser";
  public static final String PrevUser = "prevUser";
  public static final String StartEvent = "start";
  public static final String EndEvent = "end";
  public static final String CreateEvent = "create";
  public static final String CompleteEvent = "complete";
  public static final String AssignmentEvent = "assignment";

  public static final String NODE_APPROVAL_STATUS = "approvalStatus";
  public static final String NODE_APPROVAL_CONTENT = "approvalContent";
  public static final short OnLineForm = 0;
  public static final short UrlForm = 1;
  public static final String FormPkRegEx = "\\{pk\\}";

  public static final String StartScript = "start_script_content";
  public static final String AssignScript = "assign_script_content";
  public static final String CompleteScript = "complete_script_content";

  // 代办
  public static final String QUERY_MY_PROCESS_ID = "SELECT DISTINCT  PROC_INST_ID_ FROM (SELECT  task.PROC_INST_ID_  FROM  ACT_RU_TASK task "
      + "  WHERE task.proc_def_id_ LIKE '${processKey}%' AND "
      + "  task.ASSIGNEE_ =${usernameExpress}"
      + " UNION ALL "
      + "  SELECT   task.PROC_INST_ID_  FROM  act_ru_identitylink task"
      + "  WHERE task.proc_def_id_ LIKE '${processKey}%' AND "
      + "  task.USER_ID_ =${usernameExpress} AND task.type_='candidate'  ) task ";

  // 代办，不指定人员
  private static final String QUERY_PROCESS = "SELECT   PROC_INST_ID_, task.ASSIGNEE_ AS task_executer  FROM  ACT_RU_TASK task "
      + "  WHERE task.proc_def_id_ LIKE '${processKey}%' AND task.ASSIGNEE_ IS NOT NULL  "
      + " UNION ALL "
      + "  SELECT   task.PROC_INST_ID_ ,task.USER_ID_ AS task_executer FROM  act_ru_identitylink task"
      + "  WHERE task.proc_def_id_ LIKE '${processKey}%' AND task.type_='candidate' AND task.USER_ID_ IS NOT NULL   ";

  
  //
  public static final String QUERY_MY_TASK_ID = DialectFactory
      .getDialect()
      .getLimitString(
          "SELECT task.id_ FROM ACT_RU_TASK task "
              + "  WHERE task.PROC_INST_ID_='%s' AND task.ASSIGNEE_='%s'  "
              + " UNION ALL "
              + "  SELECT  task.task_id_ FROM  act_ru_identitylink task"
              + "  WHERE task.PROC_INST_ID_='%s' AND task.type_='candidate' AND task.USER_ID_='%s' ");

  public static final String QUERY_MY_ALL_PROCESS_ID = "SELECT  task.PROC_INST_ID_  FROM  ACT_RU_TASK task "
      + "  WHERE  task.ASSIGNEE_ =${usernameExpress}  UNION ALL "
      + "  SELECT  task.PROC_INST_ID_ FROM act_ru_identitylink task  "
      + "  WHERE  task.USER_ID_ = ${usernameExpress}   AND task.type_='candidate' AND task.USER_ID_ IS NOT NULL  ";

  // 与我相关的已办
  public static final String QUERY_MY_TASK_ATTEND_ID = "SELECT DISTINCT task.PROC_INST_ID_ FROM act_hi_taskinst task  "
      + " WHERE  task.PROC_DEF_ID_ LIKE '%s%%'  AND  task.ASSIGNEE_='${username}'  ";

  // 与我相关的所有已办流程ID
  public static final String QUERY_MY_TASK_ATTEND_OFALLPROCESS_ID = "SELECT DISTINCT task.PROC_INST_ID_ FROM act_hi_taskinst task  "
      + " WHERE   task.ASSIGNEE_='${username}'  ";

  // 我发起的
  public static final String QUERY_MY_TASK_STARTED_ID = "SELECT proc.PROC_INST_ID_ FROM act_hi_procinst proc "
      + " WHERE proc.PROC_DEF_ID_ LIKE '%s%%' "
      + " AND proc.START_USER_ID_='%s' ";
  //所有与我相关的（待办、发起、已办）
  public static final String QUERY_MY_TASK_RELATE_ID = "SELECT proc.PROC_INST_ID_ FROM act_hi_procinst proc "
      + " WHERE 1=1 "
      + " AND proc.START_USER_ID_='${username}' UNION ALL SELECT DISTINCT task.PROC_INST_ID_ FROM act_hi_taskinst task "
      + " WHERE 1=1  AND  task.ASSIGNEE_='${username}' "
      +" UNION ALL "
      +" SELECT  task.PROC_INST_ID_  FROM  ACT_RU_TASK task "
      + "  WHERE  task.ASSIGNEE_ ='${username}'  UNION ALL "
      + "  SELECT  task.PROC_INST_ID_ FROM act_ru_identitylink task  "
      + "  WHERE  task.USER_ID_ = '${username}'   AND task.type_='candidate' AND task.USER_ID_ IS NOT NULL  ";

  /**
   * 下一步处理人
   */
  public static final String QUERY_TASK_USERS = "SELECT  DISTINCT CASE WHEN (u2.name <>'' OR u2.name IS NOT NULL) THEN u2.name ELSE u1.name END AS NAME, CASE WHEN u2.fullname <>'' THEN u2.fullname ELSE u1.fullname END AS fullname   FROM  ACT_RU_TASK task "

      + " INNER JOIN  act_ru_identitylink ident "
      + " ON (task.ID_ = ident.TASK_ID_   OR (task.PROC_INST_ID_=ident.PROC_INST_ID_ AND task.ASSIGNEE_=ident.USER_ID_) ) "
      + " LEFT JOIN  app_user u1 ON  ident.USER_ID_ = u1.name  "
      + " LEFT JOIN  app_user u2 ON  task.ASSIGNEE_ = u2.name  "
      + " WHERE  ( task.PROC_INST_ID_='%s' OR task.ID_ IN (%s)  ) ";

  /**
   * 下一步处理人
   */
  public static final String QUERY_TASK_USERS_BUSSINESS_KEY = "SELECT  DISTINCT CASE WHEN (u2.name <>'' OR u2.name IS NOT NULL) THEN u2.name ELSE u1.name END AS NAME, CASE WHEN u2.fullname <>'' THEN u2.fullname ELSE u1.fullname END AS fullname   FROM  ACT_RU_TASK task "
      + " INNER JOIN  act_ru_execution e ON task.execution_id_ = e.ID_  "
      + " INNER JOIN  act_ru_identitylink ident "
      + " ON (task.ID_ = ident.TASK_ID_   OR (task.PROC_INST_ID_=ident.PROC_INST_ID_ AND task.ASSIGNEE_=ident.USER_ID_) ) "
      + " LEFT JOIN  app_user u1 ON  ident.USER_ID_ = u1.name  "
      + " LEFT JOIN  app_user u2 ON  task.ASSIGNEE_ = u2.name  "
      + " WHERE  ( e.business_key_='%s' ) ";

  // 查询已经指派的项目
  public static final String QUERY_TASK_ASSIGNEE = "SELECT DISTINCT name,fullname   "
      + " FROM "
      + "  ACT_RU_TASK task  "
      + "  INNER JOIN app_user u  "
      + "  ON task.ASSIGNEE_ = u.name  "
      + " WHERE   ( task.PROC_INST_ID_='%s' OR task.ID_ IN (%s)  ) ";

  public static final String QUERY_TASK_USERS_TASK_DEF = "SELECT  DISTINCT u.name,u.fullname  FROM  ACT_RU_TASK task "
      + " LEFT JOIN act_re_procdef pdef "
      + " ON task.PROC_DEF_ID_=pdef.ID_ "
      + " LEFT JOIN  act_ru_identitylink ident "
      + " ON (task.ID_ = ident.TASK_ID_   OR (task.PROC_INST_ID_=ident.PROC_INST_ID_ AND task.ASSIGNEE_=ident.USER_ID_)  ) "
      + " LEFT JOIN app_user u ON ident.USER_ID_=u.name "
      + " WHERE "
      + " ( task.PROC_INST_ID_='%s' OR task.TASK_DEF_KEY_ ='%s'  ) ";

  /**
   * 获取用户在流程中的任务id
   */
  public static final String getQueryMyTaskId(String processInstanceId,
      String username) {
    return String.format(QUERY_MY_TASK_ID, processInstanceId, username,
        processInstanceId, username);
  }

  public static final String getQueryTaskSqlCondition(String processKey,
      String processInstanceId, String username) {
    return StringUtils.replace(StringUtils.replace(QUERY_MY_PROCESS_ID,
        "${usernameExpress}", "'" + username
            + "'  AND task.PROC_INST_ID_='" + processInstanceId
            + "' "), "${processKey}", processKey)

        + DialectFactory.getDialect().getLimitString(0, 1);
  }

  /**
   * 待办
   */
  public static final String getQueryMyTaskSqlCondition(String processKey) {
    return getQueryTaskSqlCondition(processKey,
        "'" + ContextHolder.getLoginUsername() + "'");
  }

  /**
   * 所有的待办
   */
  public static final String getQueryMyAllTaskSqlCondition(String username) {
    return String.format(StringUtils.replace(" (process_id IN ("
        + QUERY_MY_ALL_PROCESS_ID
        + "AND  process_id = task.PROC_INST_ID_ ))",
        "${usernameExpress}", "'" + username + "'"));
  }

  /**
   * 某项任务代办条件语句
   */
  public static final String getQueryTaskSqlCondition(String processKey,
      String usernameExpress) {

    return StringUtils.replace(
        StringUtils.replace(" ( process_id IN (" + QUERY_MY_PROCESS_ID
            + "   ))", "${usernameExpress}", usernameExpress),
        "${processKey}", processKey);
  }

  /**
   * 某项任务代办sql
   */
  public static final String getQueryTaskSql(String processKey) {

    return StringUtils.replace(QUERY_PROCESS, "${processKey}", processKey);
  }

  /**
   * 已办
   */
  public static final String getQueryMyTaskAttendSqlCondition(
      String processKey) {

    String sql = String.format(StringUtils.replace(" (  process_id IN ("
        + QUERY_MY_TASK_ATTEND_ID + " )) ", "${username}",
        ContextHolder.getLoginUsername()), processKey);
    return sql;
  }

  /**
   * 某人的已办
   */
  public static final String getQueryMyTaskAttendSqlCondition(
      String processKey, String username) {

    String sql = String.format(StringUtils.replace(" (   process_id IN  ("
        + QUERY_MY_TASK_ATTEND_ID + " )) ", "${username}", username),
        processKey);
    return sql;
  }
  
  /**
   * 某人的相关包括（发起的、已办的、待办的）
   */
  public static final String getQueryMyAllRelateTaskSqlCondition(
      String processKey, String username) {

    String sql = String.format(StringUtils.replace(" (   EXISTS (SELECT 1 FROM  ("
        + QUERY_MY_TASK_RELATE_ID + " )t1  WHERE PROC_INST_ID_ = process_id))  ", "${username}", username),
        processKey);
    return sql;
  }

  /**
   * 某人的已办,包括所有流程
   */
  public static final String getQueryMyTaskAttendOfAllProcessSqlCondition(
      String username) {

    String sql = String.format(StringUtils.replace(" ( process_id IN ("
        + QUERY_MY_TASK_ATTEND_OFALLPROCESS_ID + "  )) ",
        "${username}", username));
    return sql;
  }

  /**
   * 已发
   */
  public static final String getQueryMyTaskStartedSqlCondition(
      String processKey) {
    return " (  process_id IN ("
        + String.format(QUERY_MY_TASK_STARTED_ID, processKey,
            ContextHolder.getLoginUsername()) + "  )) ";
  }

  /**
   * 查询下一步的处理人
   */
  public static final String getQueryUsersByTask(String processId,
      String... taskIds) {
    StringBuilder ids = new StringBuilder("'").append(taskIds[0]).append(
        "'");
    for (int i = 1; i < ids.length(); i++) {
      ids.append(",'").append(taskIds[i]).append("'");
    }
    return String.format(QUERY_TASK_USERS, processId, ids);
  }

  /**
   * 查询下一步的处理人
   */
  public static final String getQueryUsersByTask(String processId,
      List<Task> tasks) {

    StringBuilder ids = new StringBuilder("'").append(tasks.get(0).getId())
        .append("'");
    for (int i = 1; i < tasks.size(); i++) {
      ids.append(",'").append(tasks.get(i).getId()).append("'");
    }
    return String.format(QUERY_TASK_USERS, processId, ids);
  }

  /**
   * 查询下一步的处理人
   */
  public static final String getQueryUsersByBusinessKey(String businessKey) {

    return String.format(QUERY_TASK_USERS_BUSSINESS_KEY, businessKey);
  }

  /**
   * 查询下一步的处理人
   */
  public static final String getQueryAssigneeUsersByTask(String processId,
      List<Task> tasks) {

    StringBuilder ids = new StringBuilder("'").append(tasks.get(0).getId())
        .append("'");
    for (int i = 1; i < tasks.size(); i++) {
      ids.append(",'").append(tasks.get(i).getId()).append("'");
    }
    return String.format(QUERY_TASK_ASSIGNEE, processId, ids);
  }

  public static final String getQueryUsersByTask(String processId,
      String nodeId) {

    return String.format(QUERY_TASK_USERS_TASK_DEF, processId, nodeId);
  }

  public static StringBuilder getProcessListSql(String projectId, String id,
      List<String> ids, String customColumns, DataRequest req) {
    BpmProcessSettingService bpmProcessSettingService = ContextHolder
        .getSpringBean("bpmProcessSettingService");

    List<Map<String, Object>> list = bpmProcessSettingService
        .findProcessRelationList();
    return getProcessListSql(list, projectId, id, ids, customColumns, req);
  }

  /**
   * 
   * @param processKey
   * @param projectId
   */
  private static StringBuilder getProcessListSql(
      List<Map<String, Object>> list, String projectId, String id,
      List<String> ids, String customColumns, DataRequest req) {

    StringBuilder sb = new StringBuilder();

    StringBuilder idlist = new StringBuilder();
    if (ids != null && ids.size() > 0) {
      idlist.append(" ").append("'").append(ids.get(0)).append("'");

      for (int j = 1; j < ids.size(); j++) {
        idlist.append(",'").append(ids.get(j)).append("'");
      }
    }

    String idlistStr = idlist.toString();

    sb.append("SELECT   ' ' AS NAME_,'' AS  current_executer_name,t2.*");

    if (StringUtils.isNotEmpty(customColumns)) {
      sb.append(",").append(customColumns);
    }
    sb.append(" FROM(");

    boolean hasUnionAll = false;
    for (int i = 0; i < list.size(); i++) {
      Object o = list.get(i).get("deployment_name");
      if (o == null || "null".equals(o.toString())) {
        continue;
      }

      if (hasUnionAll)
        sb.append(" UNION ALL ");

      hasUnionAll = true;

      appendProcessRelationSQL(projectId, idlistStr, sb, list.get(i));
    }

    sb.append(")t2 ");

    //
    // sb.append(" INNER JOIN (");
    // if (DialectFactory.getDatabaseType() == DatabaseType.MYSQL) {
    // sb.append("SELECT  BUSINESS_KEY_,'已结束' AS NAME_,'' AS current_executer_name,def.KEY_ FROM act_hi_procinst  a");
    // sb.append(" INNER JOIN act_re_procdef def ON a.PROC_DEF_ID_=def.ID_ WHERE a.END_TIME_ IS NOT NULL");
    // if (StringUtils.isNotEmpty(idlistStr)) {
    // sb.append(" AND  BUSINESS_KEY_ IN (");
    // sb.append(idlistStr).append(")");
    // }
    //
    // sb.append(" UNION ALL ");
    // sb.append("SELECT a.BUSINESS_KEY_,b.NAME_ AS NAME_,''  AS current_executer_name,def.KEY_ FROM  act_ru_execution a ");
    // } else {
    //
    // sb.append("SELECT  BUSINESS_KEY_, CAST('已结束' AS NVARCHAR2(200)) AS NAME_,CAST('' AS NVARCHAR2(10)) AS current_executer_name,def.KEY_ FROM act_hi_procinst  a");
    // sb.append(" INNER JOIN act_re_procdef DEF ON a.PROC_DEF_ID_=DEF.ID_ WHERE a.END_TIME_ IS NOT NULL");
    //
    // if (StringUtils.isNotEmpty(idlistStr)) {
    // sb.append(" AND  BUSINESS_KEY_ IN (");
    // sb.append(idlistStr).append(")");
    // }
    // sb.append(" UNION ALL ");
    // sb.append("SELECT a.BUSINESS_KEY_,b.NAME_ AS NAME_, null AS current_executer_name,DEF.KEY_ FROM  act_ru_execution a ");
    //
    // }
    //
    // sb.append(" INNER JOIN act_re_procdef def ON a.PROC_DEF_ID_=def.ID_ INNER JOIN act_ru_task  b ON a.PROC_INST_ID_=b.PROC_INST_ID_");
    // //
    // sb.append(" INNER JOIN act_ru_identitylink c ON b.ID_=c.TASK_ID_ INNER JOIN app_user u ON c.user_id_=U.name  GROUP BY a.BUSINESS_KEY_,b.NAME_,def.KEY_ ");
    // if (StringUtils.isNotEmpty(idlistStr)) {
    // sb.append(" WHERE  BUSINESS_KEY_ IN (");
    // sb.append(idlistStr).append(")");
    // }
    //
    // sb.append(" ) t1");
    //
    // sb.append(" ON t1.BUSINESS_KEY_=t2.BUSINESS_KEY_   ");

    if (req != null && req.getFilter() != null) {
      sb.append(" WHERE 1=1 ");
      sb.append(req.getFilter().getSql());
    }

    return sb;
  }

  public static StringBuilder getProcessListSql(DataRequest req) {
    BpmProcessSettingService bpmProcessSettingService = ContextHolder
        .getSpringBean("bpmProcessSettingService");

    List<Map<String, Object>> list = bpmProcessSettingService
        .findProcessRelationList();

    StringBuilder sb = new StringBuilder();
    sb.append("SELECT * FROM (");

    boolean hasUnionAll = false;
    for (int i = 0; i < list.size(); i++) {
      Object o = list.get(i).get("deployment_name");
      if (o == null || "null".equals(o.toString())) {
        continue;
      }
      if (hasUnionAll)
        sb.append(" UNION ALL ");

      hasUnionAll = true;
      appendProcessRelationSQL(null, null, sb, list.get(i));
    }
    sb.append(")t ");

    sb.append(" WHERE 1=1 ");
    if (req != null && req.getFilter() != null) {

      sb.append(req.getFilter().getSql());
    }

    return sb;

  }

  public static void appendProcessRelationSQL(String projectId, String ids,
      StringBuilder sb, Map<String, Object> ps) {

    String key_ = ps.get("procedef_id").toString().split(":")[0];
    sb.append(
        " SELECT id AS BUSINESS_KEY_,code,process_id,create_date,creator_name,")
        .append(ps.get("process_relation_title_column"))
        .append(" AS title,'").append(ps.get("deployment_name"))
        .append("' AS process_name,'").append(key_)
        .append("' AS KEY_,status FROM ").append(ps.get("table_name"))
        .append(" WHERE 1=1");
    if (StringUtils.isNotEmpty(projectId)
        && !"knowledgeProcess".equals(key_) && !"sparepartOutProcess".equals(key_) && !"sparepartInProcess".equals(key_) && !"projectProcess".equals(key_)) {
      sb.append("  AND project_Id='");
      sb.append(projectId).append("'");
    }

    if (StringUtils.isNotEmpty(ids)) {
      sb.append("  AND id IN(");
      sb.append(ids).append(")");
    }
  }

  public static String getQueryCurrentUserSql() {
    String sql = "";
    if (DialectFactory.getDatabaseType() == DatabaseType.MYSQL) {

      sql = " SELECT  GROUP_CONCAT(DISTINCT CONCAT(U.fullname) SEPARATOR ',')  AS bpm_current_executer_name,A.BUSINESS_KEY_ AS BUSINESS_KEY  FROM  act_ru_execution  A ";

    } else {

      sql = "SELECT  CAST(WMSYS.WM_CONCAT(to_char(U.name)) AS NVARCHAR2(200))   AS bpm_current_executer,  CAST(WMSYS.WM_CONCAT(to_char(U.fullname)) AS NVARCHAR2(200))   AS bpm_current_executer_name,A.BUSINESS_KEY_  AS BUSINESS_KEY FROM  act_ru_execution  A ";
    }

    sql += " INNER JOIN act_ru_task  B ON A.PROC_INST_ID_=B.PROC_INST_ID_"
        + " INNER JOIN act_ru_identitylink C ON B.ID_=C.TASK_ID_ INNER JOIN app_user U ON C.user_id_=U.name  GROUP BY A.BUSINESS_KEY_,B.NAME_ ";

    return sql;
  }
}
